package tt.dz.dao.ext.system;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import tt.dz.dao.base.OmsCommonDao;
import tt.dz.entity.exception.OmsDaoException;
import tt.dz.entity.system.OmsUrlList;
import tt.dz.entity.system.OmsUrlTreeVO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

/**
 * 权限资源 数据操作
 * @author Administrator
 * @date 2015年12月16日
 */
@Repository
public class OmsUrlListDao extends OmsCommonDao<OmsUrlList>{

	private Log log = LogFactory.getLog(OmsUrlListDao.class);
	/**
	 * 查询所有的urlList
	 * @author Administrator
	 * @return
	 */
	public List<OmsUrlList> findAllValidUrlListEmbedList()
	{

		OmsUrlList omsUrlList = new OmsUrlList();
		//设置有效参数
		omsUrlList.setValid_flag(1);
		return super.findExample(omsUrlList, null, null, null, null);
	}

	public List<OmsUrlList> findValidUrlListByRoles(final String[] roleIds,final String level)throws OmsDaoException
	{
		StringBuilder sb = new StringBuilder();
		sb.append("select oul.url_id,oul.pre_url_id,oul.url_level,oul.url_path,oul.valid_flag from oms_url_list oul where EXISTS (select 1 from oms_role_auth ora where oul.url_id = ora.url_id and ora.role_id in (");
		for (int i =0;i <roleIds.length;i++)
		{
			if (i > 0)
			{
				sb.append(",");
			}

			sb.append("?");
		}
		sb.append(")) and url_level = ? and valid_flag =1");
		try
		{
		return this.getJdbcTemplate().query(sb.toString(), new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement arg0) throws SQLException
			{
				int index = 1;
				for (String role : roleIds)
				{
					arg0.setString(index++, role);
				}
				arg0.setString(index++, level);
			}
		},new BeanPropertyRowMapper<OmsUrlList>(OmsUrlList.class));
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}

	public List<OmsUrlList> findValidUrlListByRoleId(final String roleId,final String level,final boolean isExists)throws OmsDaoException
	{
		StringBuilder sb = new StringBuilder();
		sb.append("select oul.url_id,oul.pre_url_id,oul.url_level,oul.url_path,oul.valid_flag from oms_url_list oul where ");
		if (!isExists)
		{
			sb.append(" NOT ");
		}
		sb.append(" EXISTS (select 1 from oms_role_auth ora where oul.url_id = ora.url_id and ora.role_id = ").
		append("? and url_level = ? and valid_flag =1");
		try
		{
		return this.getJdbcTemplate().query(sb.toString(), new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement arg0) throws SQLException
			{
				int index = 1;
				arg0.setString(index++, roleId);
				arg0.setString(index++, level);
			}
		},new BeanPropertyRowMapper<OmsUrlList>(OmsUrlList.class));
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}


	/**
	 * 根据管理员Id 查询管理员的管理的操作URL
	 * @author huanghao
	 * @param managerId
	 * @param levelType
	 * @return
	 * @throws OmsDaoException
	 */
	public List<OmsUrlList> findValidUrlListByManagerId(String managerId, Integer levelType) throws OmsDaoException
	{
		try
		{
			//			String sql = "select *	from oms_url_list oul where EXISTS (select 1 from oms_role_auth ora where oul.url_id = ora.url_id and ora.role_id in (	select role_id from oms_oper_role where oper_id = ?	 and valid_flag =1)) and url_level = ? and valid_flag =1 order by sortCol asc";
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT * ");
			sql.append(" FROM oms_url_list oul ");
			sql.append(" WHERE url_level < ? AND valid_flag = 1 AND ( ");
			sql.append(" 		EXISTS ( ");
			sql.append(" 			SELECT 1 ");
			sql.append(" 			FROM oms_role_auth ora ");
			sql.append(" 			WHERE oul.url_id = ora.url_id AND ora.role_id IN ( ");
			sql.append(" 					SELECT role_id ");
			sql.append(" 					FROM oms_oper_role ");
			sql.append(" 					WHERE oper_id = ? AND valid_flag = 1 ");
			sql.append(" 					) ");
			sql.append(" 			) OR EXISTS ( ");
			sql.append(" 			SELECT 1 ");
			sql.append(" 			FROM oms_oper_list u ");
			sql.append(" 			INNER JOIN oms_operation_sp_list s ON u.entity_id = s.oper_merchant_id ");
			sql.append(" 			WHERE u.oper_id = ? AND s.super_flag = 1 AND s.valid_flag = 1 AND u.valid_flag = 1 ");
			sql.append(" 			) ");
			sql.append(" 		) ");
			sql.append(" ORDER BY sortCol ASC ");

			return this.getJdbcTemplate().query(sql.toString(), new Object[] { levelType, managerId, managerId }, new BeanPropertyRowMapper<>(OmsUrlList.class));
		}
		catch (Exception e)
		{
			throw new OmsDaoException(e);
		}
	}

	/**
	 * 根据管理员Id 查询管理员的管理的操作URL
	 * @param managerId
	 * @return
	 * @throws OmsDaoException
	 */
	public List<String> findValidUrlListByManagerId(String managerId)throws OmsDaoException
	{
		try
		{
			String sql = "select url_path from oms_url_list oul where EXISTS (select 1 from oms_role_auth ora where oul.url_id = ora.url_id and ora.role_id in (	select role_id from oms_oper_role where oper_id = ?	 and valid_flag =1))  and valid_flag =1";
			return this.getJdbcTemplate().queryForList(sql, new Object[] { managerId }, String.class);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}

	/**
	 * 根据管理员Id 查询管理员的管理的操作URL
	 * @author huanghao
	 * @param managerId
	 * @param levelType
	 * @return
	 * @throws OmsDaoException
	 */
	public List<String> findValidUrlsByManagerId(String managerId,String levelType)throws OmsDaoException
	{
		try
		{
			String sql = "select url_path	from oms_url_list oul where EXISTS (select 1 from oms_role_auth ora where oul.url_id = ora.url_id and ora.role_id in (	select role_id from oms_oper_role where oper_id = ?	 and valid_flag =1)) and url_level = ? and valid_flag =1";
			return this.getJdbcTemplate().queryForList(sql, new Object[]{managerId,levelType}, String.class);
		}
		catch(Exception e)
		{
			throw new OmsDaoException(e);
		}
	}

	public List<OmsUrlList> findUrlListByRoleId(final String roleId) throws OmsDaoException {
		StringBuilder sb = new StringBuilder();
		sb.append("select oul.url_id,oul.pre_url_id,oul.url_level,oul.url_path,oul.valid_flag ,oul.url_name,oul.pre_url_name,IF(ora.role_id = ?,1,0) as is_role   "
				+ "from oms_url_list oul left OUTER join  oms_role_auth ora ON  oul.url_id = ora.url_id  and  "
				+ "ora.role_id=? and oul.valid_flag = 1 ");
		return this.getJdbcTemplate().query(sb.toString(), new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement arg0) throws SQLException
			{
				int index = 1;
				arg0.setString(index++, roleId);
				arg0.setString(index++, roleId);
			}
		},new BeanPropertyRowMapper<OmsUrlList>(OmsUrlList.class));

	}

	public OmsUrlTreeVO getAllUrls(String roleId)
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT u.url_id, u.url_name, u.url_path, u.url_level, u.pre_url_id, u.is_leaf, u.sortCol, u.icon, ");
		sql.append("   IFNULL((select 1 from oms_role_auth a where a.role_id = ? and a.url_id = u.url_id and a.valid_flag = 1), 0) checked ");
		sql.append(" FROM oms_url_list u ");
		sql.append(" WHERE u.valid_flag = 1 ");
		sql.append(" ORDER BY u.url_level, u.pre_url_id, u.sortCol ");
		List<OmsUrlTreeVO> list = this.getJdbcTemplate().query(sql.toString(), new Object[] { roleId }, new RowMapper()
		{
			@Override
			public OmsUrlTreeVO mapRow(ResultSet rs, int rowNum) throws SQLException
			{
				OmsUrlTreeVO vo = new OmsUrlTreeVO();
				vo.setId(rs.getString("url_id"));
				vo.setName(rs.getString("url_name"));
				vo.setUrl(rs.getString("url_path"));
				vo.setLevel(rs.getInt("url_level"));
				vo.setParent(rs.getString("pre_url_id"));
				vo.setLeaf(rs.getInt("is_leaf") == 1);
				vo.setOrder(rs.getInt("sortCol"));
				vo.setIcon(rs.getString("icon"));
				vo.setChecked(rs.getInt("checked") == 1);
				return vo;
			}
		});

		OmsUrlTreeVO root = new OmsUrlTreeVO();
		root.setId("root");
		root.setName("root");

		HashMap<String, OmsUrlTreeVO> map = new HashMap<>();
		for (OmsUrlTreeVO vo : list)
		{
//			log.info(vo.getId());
//			log.info(vo.getParent());
//			log.info(vo.getLevel());
			map.put(vo.getId(), vo);
			if ("root".equals(vo.getParent()))
			{
				root.getSubTree().add(vo);
			}
			else
			{
				OmsUrlTreeVO parent = map.get(vo.getParent());
				if (parent != null)
					parent.getSubTree().add(vo);
			}
		}

		return root;

	}

	public OmsUrlTreeVO getAllUrls()
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT u.url_id, u.url_name, u.url_path, u.url_level, u.pre_url_id, u.is_leaf, u.sortCol, u.icon ");
		sql.append(" FROM oms_url_list u ");
		sql.append(" WHERE u.valid_flag = 1 ");
		sql.append(" ORDER BY u.url_level, u.pre_url_id, u.sortCol ");
		List<OmsUrlTreeVO> list = this.getJdbcTemplate().query(sql.toString(), new RowMapper()
		{
			@Override
			public OmsUrlTreeVO mapRow(ResultSet rs, int rowNum) throws SQLException
			{
				OmsUrlTreeVO vo = new OmsUrlTreeVO();
				vo.setId(rs.getString("url_id"));
				vo.setName(rs.getString("url_name"));
				vo.setUrl(rs.getString("url_path"));
				vo.setLevel(rs.getInt("url_level"));
				vo.setParent(rs.getString("pre_url_id"));
				vo.setLeaf(rs.getInt("is_leaf") == 1);
				vo.setOrder(rs.getInt("sortCol"));
				vo.setIcon(rs.getString("icon"));
				return vo;
			}
		});

		OmsUrlTreeVO root = new OmsUrlTreeVO();
		root.setId("root");
		root.setName("root");

		HashMap<String, OmsUrlTreeVO> map = new HashMap<>();
		for (OmsUrlTreeVO vo : list)
		{
			map.put(vo.getId(), vo);
			if ("root".equals(vo.getParent()))
			{
				root.getSubTree().add(vo);
			}
			else
			{
				OmsUrlTreeVO parent = map.get(vo.getParent());
				if (parent != null)
					parent.getSubTree().add(vo);
			}
		}

		return root;

	}
}
